package com.aries.business;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class ExpenseDataRepository {
	private static final String COMMA = ",";
	private static final String SPACE = " ";
	private static final String DBTYPE_TEXT = "text";
	private static final String DBTYPE_INT = "integer";	
	private static final String DBTYPE_DATETIME = "datetime";	
	private static final String DBTYPE_DOUBLE = "double";	
	
	private static final String SQL_CREATE_SCHEMA =
		    "CREATE TABLE " + ExpenseTableContract.TABLE_NAME + " (" +
		    		ExpenseTableContract._ID + " INTEGER PRIMARY KEY" + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_TITLE + SPACE + DBTYPE_TEXT + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_AMOUNT + SPACE + DBTYPE_DOUBLE + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_TYPE + SPACE + DBTYPE_INT + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_DATE + SPACE + DBTYPE_DATETIME + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_SUMMARY + SPACE + DBTYPE_TEXT + COMMA +
		    		ExpenseTableContract.COLUMN_NAME_CREATETIME + SPACE + DBTYPE_DATETIME +
		    " )";

	
	public static abstract class ExpenseTableContract implements BaseColumns{
	    public static final String TABLE_NAME = "expense";

	    //public static final String COLUMN_NAME_ID = "ID";
	    public static final String COLUMN_NAME_TITLE = "Title";
	    public static final String COLUMN_NAME_AMOUNT = "Amount";
	    public static final String COLUMN_NAME_TYPE = "Type";
	    public static final String COLUMN_NAME_DATE = "Date";
	    public static final String COLUMN_NAME_SUMMARY = "Summary";	
	    public static final String COLUMN_NAME_CREATETIME = "CreateTime";	
	    
	    public static final String COLUMN_NAME_NULLABLE = "null";
	}

	public class ExpenseDataHelper extends SQLiteOpenHelper{	
		
		public static final int DATABASE_VERSION = 1;
		private static final String DATABASE_NAME="wallet.db";
		
		public ExpenseDataHelper(Context context) {
	        super(context, DATABASE_NAME, null, DATABASE_VERSION);
	    }
		
		public void onCreate(SQLiteDatabase db){
			db.execSQL(SQL_CREATE_SCHEMA);
		}		
		
		public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion){
			
		}
	}
	
	private ExpenseDataHelper dataHelper;
	
	public ExpenseDataRepository(Context context){
		dataHelper = new ExpenseDataHelper(context);	}
			
	private SimpleDateFormat dateFormattor = new SimpleDateFormat("yyyy-MM-dd");	
	private SimpleDateFormat timeFormattor = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	public long Insert(ExpenseEntity expense){
		SQLiteDatabase db = dataHelper.getWritableDatabase();

		// Create a new map of values, where column names are the keys
		ContentValues values = new ContentValues();
		values.put(ExpenseTableContract.COLUMN_NAME_TITLE, expense.getTitle());
		values.put(ExpenseTableContract.COLUMN_NAME_AMOUNT, expense.getAmount());
		values.put(ExpenseTableContract.COLUMN_NAME_TYPE, expense.getType());
		values.put(ExpenseTableContract.COLUMN_NAME_DATE, dateFormattor.format(expense.getDate()));
		values.put(ExpenseTableContract.COLUMN_NAME_SUMMARY, expense.getSummary());
		values.put(ExpenseTableContract.COLUMN_NAME_CREATETIME, timeFormattor.format(expense.getCreateTime()));

		// Insert the new row, returning the primary key value of the new row
		return db.insert(ExpenseTableContract.TABLE_NAME, ExpenseTableContract.COLUMN_NAME_NULLABLE, values);
	}
	
	public void Delete(String id){
		SQLiteDatabase db = dataHelper.getWritableDatabase();
		
		// Define 'where' part of query.
		String selection = ExpenseTableContract._ID + " = ?";
		
		// Specify arguments in placeholder order.
		String[] selelectionArgs = { id };
		
		// Issue SQL statement.
		db.delete(ExpenseTableContract.TABLE_NAME, selection, selelectionArgs);
	}
	
	public void Delete(){
		SQLiteDatabase db = dataHelper.getWritableDatabase();
		db.execSQL("DELETE FROM " + ExpenseTableContract.TABLE_NAME);
	}
	
	public void Update(ExpenseEntity expense){
		SQLiteDatabase db = dataHelper.getWritableDatabase();

		// New value for one column
		ContentValues values = new ContentValues();
		values.put(ExpenseTableContract.COLUMN_NAME_TITLE, expense.getTitle());
		values.put(ExpenseTableContract.COLUMN_NAME_AMOUNT, expense.getAmount());
		values.put(ExpenseTableContract.COLUMN_NAME_TYPE, expense.getType());
		values.put(ExpenseTableContract.COLUMN_NAME_DATE, dateFormattor.format(expense.getDate()));
		values.put(ExpenseTableContract.COLUMN_NAME_SUMMARY, expense.getSummary());
		values.put(ExpenseTableContract.COLUMN_NAME_CREATETIME, timeFormattor.format(expense.getCreateTime()));

		// Which row to update, based on the ID
		String selection = ExpenseTableContract._ID + " = ?";
		String[] selectionArgs = { expense.getID() };

		db.update(ExpenseTableContract.TABLE_NAME, values, selection, selectionArgs);
	}
	
	public List<ExpenseEntity> Retrieve(){
		SQLiteDatabase db = dataHelper.getReadableDatabase();

		// Define a projection that specifies which columns from the database
		// you will actually use after this query.
		String[] fields = {
			ExpenseTableContract._ID,
		    ExpenseTableContract.COLUMN_NAME_TITLE,
		    ExpenseTableContract.COLUMN_NAME_AMOUNT,
		    ExpenseTableContract.COLUMN_NAME_TYPE,
		    ExpenseTableContract.COLUMN_NAME_DATE,
		    ExpenseTableContract.COLUMN_NAME_SUMMARY,
		    ExpenseTableContract.COLUMN_NAME_CREATETIME
	    };

		// How you want the results sorted in the resulting Cursor
		String sortOrder = ExpenseTableContract.COLUMN_NAME_CREATETIME + " DESC";

		Cursor reader = db.query(
			ExpenseTableContract.TABLE_NAME,  // The table to query
			fields,                       	  // The columns to return
		    null,                             // The columns for the WHERE clause
		    null,                             // The values for the WHERE clause
		    null,                             // don't group the rows
		    null,                             // don't filter by row groups
		    sortOrder                         // The sort order
		);
		
		return ReadEntity(reader);
	}
	
	public void CreateTestData(){
		java.util.Date now = new java.util.Date();
		for(int i=0;i<5;i++){
			ExpenseEntity expense = new ExpenseEntity();
			
			expense.setTitle("Expense NO."+String.valueOf(i));
			expense.setAmount(10.0);
			expense.setType(1);
			expense.setDate(now);
			expense.setSummary("Summary "+String.valueOf(i));
			expense.setCreateTime(now);
			
			this.Insert(expense);
		}
	}
	
	private ArrayList<ExpenseEntity> ReadEntity(Cursor reader){
		ArrayList<ExpenseEntity> list = new ArrayList<ExpenseEntity>();
		
		while(reader.moveToNext()){
			ExpenseEntity expense = new ExpenseEntity();
			
			expense.setID(reader.getString(reader.getColumnIndexOrThrow(ExpenseTableContract._ID)));
			expense.setTitle(reader.getString(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_TITLE)));
			expense.setAmount(reader.getDouble(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_AMOUNT)));
			expense.setType(reader.getInt(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_TYPE)));
			expense.setSummary(reader.getString(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_SUMMARY)));

				try {
					expense.setDate(dateFormattor.parse(reader.getString(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_DATE))));
					expense.setCreateTime(timeFormattor.parse(reader.getString(reader.getColumnIndexOrThrow(ExpenseTableContract.COLUMN_NAME_CREATETIME))));
				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					//e.printStackTrace();

					java.util.Date now = new java.util.Date();
					expense.setDate(now);
					expense.setCreateTime(now);
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					//e.printStackTrace();

					java.util.Date now = new java.util.Date();
					expense.setDate(now);
					expense.setCreateTime(now);
				}

			list.add(expense);
		}
		
		return list;
	}
}
